/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package ua.kpi.transport.db.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import ua.kpi.transport.db.dao.factory.DAOFactory;
import ua.kpi.transport.db.dao.factory.DBEnum;
import ua.kpi.transport.db.dao.interfaces.IStationDAO;
import ua.kpi.transport.db.dao.interfaces.IStationToTypeDAO;
import ua.kpi.transport.db.dao.interfaces.ITransportTypeDAO;
import ua.kpi.transport.db.dao.utils.MySQLQueryExecutioner;
import ua.kpi.transport.db.entities.StationBean;
import ua.kpi.transport.db.entities.StationToTypeBean;
import ua.kpi.transport.db.entities.TransportTypeBean;

/**
 *
 * @author harker777
 */
public class MySQLTransportTypeDAO implements ITransportTypeDAO {

    @Override
    public TransportTypeBean find(Integer id) {
        String query = "SELECT * FROM transporttype WHERE Id = ?";
        ResultSet rs = MySQLQueryExecutioner.execute(
                query, id);

        return fromResultSet(rs).get(0);
    }

    @Override
    public List<TransportTypeBean> findAll() {
        String query = "SELECT * FROM transporttype ORDER BY Id ASC";
        ResultSet rs = MySQLQueryExecutioner.execute(
                query);

        return fromResultSet(rs);
    }

    @Override
    public TransportTypeBean create() {
        String query = "INSERT INTO transporttype VALUES (NULL,?)";
        MySQLQueryExecutioner.executeUpdate(query, new Object[]{"Name"});

        query = "SELECT * FROM transporttype ORDER BY Id DESC LIMIT 1";
        ResultSet rs = MySQLQueryExecutioner.execute(query);
        TransportTypeBean result = fromResultSet(rs).get(0);

        // Delete old entries
        IStationToTypeDAO sttd = DAOFactory.getDAOFactory(DBEnum.MYSQL).getStationToTypeDAO();
        sttd.delete(sttd.findByTransportType(result));

        IStationDAO std = DAOFactory.getDAOFactory(DBEnum.MYSQL).getStationDAO();
        for (StationBean station : std.findAll()) {
            StationToTypeBean stationToType = sttd.create();
            stationToType.setStationId(station.getId());
            stationToType.setTransportTypeId(result.getId());
            stationToType.setStatus(false);

            sttd.update(stationToType);
        }

        return result;
    }

    @Override
    public void delete(TransportTypeBean bean) {
        IStationToTypeDAO sttd = DAOFactory.getDAOFactory(DBEnum.MYSQL).getStationToTypeDAO();
        sttd.delete(sttd.findByTransportType(bean));

        String query = "DELETE FROM transporttype WHERE Id = ?";
        MySQLQueryExecutioner.executeUpdate(
                query, bean.getId());
    }

    @Override
    public void delete(List<TransportTypeBean> beans) {
        for (TransportTypeBean bean : beans) {
            delete(bean);
        }
    }

    @Override
    public void update(TransportTypeBean bean) {
        String query = ""
                + "UPDATE transporttype "
                + "SET Name = ? "
                + "WHERE (Id = ?)";
        MySQLQueryExecutioner.executeUpdate(
                query, bean.getName(), bean.getId());
    }

    @Override
    public void update(List<TransportTypeBean> beans) {
        for (TransportTypeBean bean : beans) {
            update(bean);
        }
    }

    @Override
    public List<TransportTypeBean> findByStation(StationBean station) {
        String query = ""
                + "SELECT tt.Id, tt.Name "
                + "FROM transporttype tt, stationtotype st "
                + "WHERE (st.StationId = ?) AND "
                + "      (tt.Id = st.TransportTypeId)";
        ResultSet rs = MySQLQueryExecutioner.execute(
                query, station.getId());
        if (rs == null) {
            return null;
        }
        return fromResultSet(rs);
    }

    @Override
    public List<TransportTypeBean> fromResultSet(ResultSet rs) {
        ArrayList<TransportTypeBean> res = new ArrayList<TransportTypeBean>();
        try {
            while (rs.next()) {
                TransportTypeBean b = new TransportTypeBean(rs.getInt("Id"));
                b.setName(rs.getString("Name"));
                res.add(b);
            }
        } catch (SQLException ex) {
            //TODO: Log
            System.out.println("Cannot get TransportTypeBean");
        }
        return res;
    }
}
